❮

Question about user/database-level parameters

  • Jump to comment-1
    japinli@hotmail.com2022-08-01T10:24:33+00:00
    Hi, hackers When I try to modify the parameters for all users in the following command [1] (the library doesn't exist), and I quit the connection, I cannot log in the database, how can I bypass this checking? I find those parameters loaded by process_settings(), and it seems no way to disable this loading process. If we could bypass this checking, how can we fix these parameters? [1] postgres=# ALTER ROLE all SET local_preload_libraries TO fdafd; ALTER ROLE postgres=# \q $ psql postgres psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL: could not access file "$libdir/plugins/fdafd": No such file or directory -- Regrads, Japin Li. ChengDu WenWu Information Technology Co.,Ltd.
    • Jump to comment-1
      horikyota.ntt@gmail.com2022-08-02T05:01:34+00:00
      At Mon, 01 Aug 2022 18:24:33 +0800, Japin Li <japinli@hotmail.com> wrote in > > Hi, hackers > > When I try to modify the parameters for all users in the following command [1] > (the library doesn't exist), and I quit the connection, I cannot log in the > database, how can I bypass this checking? > > I find those parameters loaded by process_settings(), and it seems no way to > disable this loading process. If we could bypass this checking, how can we > fix these parameters? > > [1] > postgres=# ALTER ROLE all SET local_preload_libraries TO fdafd; > ALTER ROLE > postgres=# \q > > $ psql postgres > psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL: could not access file "$libdir/plugins/fdafd": No such file or directory Can you run the server in single-user mode? That mode doesn't try loading libraries. regareds. -- Kyotaro Horiguchi NTT Open Source Software Center
      • Jump to comment-1
        japinli@hotmail.com2022-08-02T05:33:22+00:00
        On Tue, 02 Aug 2022 at 13:01, Kyotaro Horiguchi <horikyota.ntt@gmail.com> wrote: > At Mon, 01 Aug 2022 18:24:33 +0800, Japin Li <japinli@hotmail.com> wrote in >> >> Hi, hackers >> >> When I try to modify the parameters for all users in the following command [1] >> (the library doesn't exist), and I quit the connection, I cannot log in the >> database, how can I bypass this checking? >> >> I find those parameters loaded by process_settings(), and it seems no way to >> disable this loading process. If we could bypass this checking, how can we >> fix these parameters? >> >> [1] >> postgres=# ALTER ROLE all SET local_preload_libraries TO fdafd; >> ALTER ROLE >> postgres=# \q >> >> $ psql postgres >> psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL: could not access file "$libdir/plugins/fdafd": No such file or directory > > Can you run the server in single-user mode? That mode doesn't try > loading libraries. > Yeah, the single-user mode works. Thank you very much! However, if the database is in production, we cannot go into single-user mode, should we provide an option to change this behavior on the fly? -- Regrads, Japin Li. ChengDu WenWu Information Technology Co.,Ltd.
        • Jump to comment-1
          daniel@manitou-mail.org2022-08-04T11:29:57+00:00
          Japin Li wrote: > However, if the database is in production, we cannot go into single-user > mode, should we provide an option to change this behavior on the fly? It already exists, through PGOPTIONS, which appears to work for local_preload_libraries, in a quick test. That is, you can log in by invoking psql with: PGOPTIONS="-c local_preload_libraries=" and issue the ALTER USER to reset things back to normal without stopping the instance. Best regards, -- Daniel Vérité https://postgresql.verite.pro/ Twitter: @DanielVerite
          • Jump to comment-1
            japinli@hotmail.com2022-08-04T15:03:47+00:00
            On Thu, 04 Aug 2022 at 19:29, Daniel Verite <daniel@manitou-mail.org> wrote: > Japin Li wrote: > >> However, if the database is in production, we cannot go into single-user >> mode, should we provide an option to change this behavior on the fly? > > It already exists, through PGOPTIONS, which appears to work > for local_preload_libraries, in a quick test. > > That is, you can log in by invoking psql with: > PGOPTIONS="-c local_preload_libraries=" > and issue the ALTER USER to reset things back to normal > without stopping the instance. Oh, great! Thank you very much! -- Regrads, Japin Li. ChengDu WenWu Information Technology Co.,Ltd.
        • Jump to comment-1
          tgl@sss.pgh.pa.us2022-08-02T05:44:50+00:00
          Japin Li <japinli@hotmail.com> writes: > Yeah, the single-user mode works. Thank you very much! > However, if the database is in production, we cannot go into single-user mode, > should we provide an option to change this behavior on the fly? There is not, and never will be, a version of Postgres in which it's impossible for a superuser to shoot himself in the foot. Test your settings more carefully before applying them to a production database that you can't afford to mess up. regards, tom lane
          • Jump to comment-1
            japinli@hotmail.com2022-08-02T05:59:39+00:00
            On Tue, 02 Aug 2022 at 13:44, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Japin Li <japinli@hotmail.com> writes: >> Yeah, the single-user mode works. Thank you very much! >> However, if the database is in production, we cannot go into single-user mode, >> should we provide an option to change this behavior on the fly? > > There is not, and never will be, a version of Postgres in which > it's impossible for a superuser to shoot himself in the foot. > > Test your settings more carefully before applying them to a > production database that you can't afford to mess up. > Thanks for your explanation! Got it. -- Regrads, Japin Li. ChengDu WenWu Information Technology Co.,Ltd.